package homework.day01;

import java.net.ConnectException;
import java.sql.*;


public class jdbcTest {
    private static final String DRIVER_CLASS="com.mysql.cj.jdbc.Driver";
    private static final String DATABASE_URL = "jdbc:mysql://localhost:3306/student?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
    private static final String USER_NAME="root";
    private static final String USER_PASSWORD="19891210";

    static{
        try {
            Class.forName(DRIVER_CLASS);
        } catch (ClassNotFoundException e) {
            System.out.println(e.getMessage());
        }
    }
    //增加学生信息
    private static void add(){
        Connection colt=null;
        PreparedStatement ps=null;
        int rows=0;
        try {
            colt=DriverManager.getConnection(DATABASE_URL,USER_NAME,USER_PASSWORD);
            String sql="insert into student(Sno,Sname,Ssex,Sage,Sdept) value(?,?,?,?,?)";
            ps=colt.prepareStatement(sql);
            ps.setInt(1,4);
            ps.setString(2,"葛鹏飞");
            ps.setString(3,"男");
            ps.setInt(4,23);
            ps.setString(5,"计算机");
            rows=ps.executeUpdate();
            if(rows==1){
                System.out.println("更新成功");
            }else{
                System.out.println("更新失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(ps!=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(colt!=null){
                try {
                    colt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }
        }
    }
    private static void modify(){
        Connection conc=null;
        PreparedStatement ps=null;
        int rows=0;
        try {
            conc=DriverManager.getConnection(DATABASE_URL,USER_NAME,USER_PASSWORD);
            String sql="update student set Sname=?,Ssex=?,Sage=? where Sno=?";
            ps=conc.prepareStatement(sql);
            ps.setString(1,"曹勇");
            ps.setString(2,"男");
            ps.setInt(3,20);
            ps.setInt(4,1);
            rows=ps.executeUpdate();
            if(rows==1){
                System.out.println("数据修改成功");
            }else{
                System.out.println("数据修改失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(ps!=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conc!=null){
                try {
                    conc.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }
        }

    }

    public static void delete(){
        Connection cnct=null;
        PreparedStatement ps=null;
        int rows=0;
        try {
            cnct=DriverManager.getConnection(DATABASE_URL,USER_NAME,USER_PASSWORD);
            String sql="delete from student where Sno=?";
            ps=cnct.prepareStatement(sql);
            ps.setInt(1,1);
            rows=ps.executeUpdate();
            if(rows==1){
                System.out.println("数据删除成功");
            }else{
                System.out.println("数据删除失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(ps!=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(cnct!=null){
                try {
                    cnct.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }
        }
    }
    private static void query(){
        Connection cnnt=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            cnnt=DriverManager.getConnection(DATABASE_URL,USER_NAME,USER_PASSWORD);
            String sql="select Sno,Sname,Ssex,Sage,Sdept from student";
            ps=cnnt.prepareStatement(sql);
            rs=ps.executeQuery();
            System.out.println("编号\t姓名\t\t性别\t年龄\t系名称");
            while(rs.next()){
                int id=rs.getInt("Sno");
                String name=rs.getString("Sname");
                String sex=rs.getString("Ssex");
                int age=rs.getInt("Sage");
                String dept=rs.getString("Sdept");
                System.out.println(id+"\t"+name+"\t"+sex+"\t"+age+"\t"+dept);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        //add();
        //modify();
        //delete();
        query();
    }

}
